package com.ruoyi.common.sql;

public class ComplexLeftJoinExample {
    public static void main(String[] args) {
        // 构建一个子查询
        SelectQuery subQuery = SelectQuery.builder()
                .table(Table.of("departments").as("d"))
                .column("d.id")
                .where(Condition.eq("d.name", "Engineering"))
                .build();

        // 构建主查询
        SelectQuery query = SelectQuery.builder()
                .distinct()
                .table(Table.of("employees").as("e"))
                .table(Table.of("departments").as("dept")
                        .joinType(JoinTypeEnum.LEFT)
                        .on(Condition.eq("e.dept_id", "dept.id")))
                .table(Table.of("salaries").as("s")
                        .joinType(JoinTypeEnum.LEFT)
                        .on(Condition.and(
                                Condition.eq("s.employee_id", "e.id"),
                                Condition.gt("s.amount", 50000)
                        )))
                .column("e.id")
                .column("e.name")
                .column("dept.name as department_name")
                .column("COALESCE(s.amount, 0) as salary")
                .where(Condition.and(
                        Condition.eq("e.status", "active"),
                        Condition.or(
                                Condition.in("e.dept_id", subQuery),
                                Condition.eq("e.is_manager", true)
                        )
                ))
                .groupBy(GroupBy.of("e.id", "e.name", "dept.name", "s.amount"))
                .having(Having.of(Condition.gt("COALESCE(s.amount, 0)", 30000)))
                .orderBy(OrderBy.desc("salary"))
                .limit(10)
                .offset(5)
                .build();

        String sql = query.build();
        System.out.println("Generated SQL:");
        System.out.println(sql);
    }
}
